import { Pool } from 'pg'

// 看板功能数据库迁移
export async function addKanbanSupport(pool: Pool): Promise<void> {
  const client = await pool.connect()
  
  try {
    console.log('🔄 开始添加看板视图支持...')
    
    // 1. 为tasks表添加看板相关字段
    await client.query(`
      ALTER TABLE tasks 
      ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
      ADD COLUMN IF NOT EXISTS board_position INTEGER DEFAULT 0
    `)
    
    console.log('✅ 任务表字段添加完成')
    
    // 2. 为现有任务设置默认状态
    await client.query(`
      UPDATE tasks 
      SET status = CASE 
        WHEN completed = true THEN 'done'
        ELSE 'todo'
      END
      WHERE status IS NULL OR status = ''
    `)
    
    console.log('✅ 现有任务状态初始化完成')
    
    // 3. 创建看板配置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS kanban_columns (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        status TEXT NOT NULL,
        title TEXT NOT NULL,
        color TEXT NOT NULL DEFAULT '#6b7280',
        column_order INTEGER NOT NULL DEFAULT 0,
        is_visible BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        
        UNIQUE(user_id, status)
      )
    `)
    
    console.log('✅ 看板列配置表创建完成')
    
    // 4. 为每个用户创建默认看板列配置
    await client.query(`
      INSERT INTO kanban_columns (user_id, status, title, color, column_order)
      SELECT DISTINCT 
        user_id,
        'todo' as status,
        '待处理' as title,
        '#6b7280' as color,
        0 as column_order
      FROM tasks 
      WHERE user_id IS NOT NULL
      ON CONFLICT (user_id, status) DO NOTHING
    `)
    
    await client.query(`
      INSERT INTO kanban_columns (user_id, status, title, color, column_order)
      SELECT DISTINCT 
        user_id,
        'in_progress' as status,
        '进行中' as title,
        '#3b82f6' as color,
        1 as column_order
      FROM tasks 
      WHERE user_id IS NOT NULL
      ON CONFLICT (user_id, status) DO NOTHING
    `)
    
    await client.query(`
      INSERT INTO kanban_columns (user_id, status, title, color, column_order)
      SELECT DISTINCT 
        user_id,
        'review' as status,
        '待审核' as title,
        '#f59e0b' as color,
        2 as column_order
      FROM tasks 
      WHERE user_id IS NOT NULL
      ON CONFLICT (user_id, status) DO NOTHING
    `)
    
    await client.query(`
      INSERT INTO kanban_columns (user_id, status, title, color, column_order)
      SELECT DISTINCT 
        user_id,
        'done' as status,
        '已完成' as title,
        '#10b981' as color,
        3 as column_order
      FROM tasks 
      WHERE user_id IS NOT NULL
      ON CONFLICT (user_id, status) DO NOTHING
    `)
    
    console.log('✅ 默认看板列配置创建完成')
    
    // 5. 创建索引提高查询性能
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_tasks_status_user_id 
      ON tasks(status, user_id) 
      WHERE status IS NOT NULL
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_tasks_board_position 
      ON tasks(board_position) 
      WHERE board_position IS NOT NULL
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_kanban_columns_user_id 
      ON kanban_columns(user_id, column_order)
    `)
    
    console.log('✅ 索引创建完成')
    
    console.log('🎉 看板视图支持添加成功！')
    
  } catch (error) {
    console.error('❌ 添加看板视图支持失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 获取用户的看板配置
export async function getUserKanbanColumns(pool: Pool, userId: string) {
  const client = await pool.connect()
  
  try {
    let result = await client.query(`
      SELECT 
        id,
        status,
        title,
        color,
        column_order,
        is_visible
      FROM kanban_columns 
      WHERE user_id = $1 
      ORDER BY column_order ASC
    `, [userId])
    
    // 如果用户没有看板列配置，创建默认配置
    if (result.rows.length === 0) {
      console.log(`🔧 为用户 ${userId} 创建默认看板列配置`)
      
      const defaultColumns = [
        { status: 'todo', title: '待处理', color: '#6b7280', order: 0 },
        { status: 'in_progress', title: '进行中', color: '#3b82f6', order: 1 },
        { status: 'review', title: '待审核', color: '#f59e0b', order: 2 },
        { status: 'done', title: '已完成', color: '#10b981', order: 3 }
      ]
      
      for (const column of defaultColumns) {
        await client.query(`
          INSERT INTO kanban_columns (user_id, status, title, color, column_order)
          VALUES ($1, $2, $3, $4, $5)
          ON CONFLICT (user_id, status) DO NOTHING
        `, [userId, column.status, column.title, column.color, column.order])
      }
      
      // 重新查询创建的列配置
      result = await client.query(`
        SELECT 
          id,
          status,
          title,
          color,
          column_order,
          is_visible
        FROM kanban_columns 
        WHERE user_id = $1 
        ORDER BY column_order ASC
      `, [userId])
    }
    
    return result.rows
  } catch (error) {
    console.error('获取看板列配置失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 更新任务状态和位置
export async function updateTaskStatus(
  pool: Pool, 
  taskId: string, 
  userId: string, 
  status: string, 
  position: number = 0
) {
  const client = await pool.connect()
  
  try {
    // 验证状态值
    const validStatuses = ['todo', 'in_progress', 'review', 'done']
    if (!validStatuses.includes(status)) {
      throw new Error(`Invalid status: ${status}`)
    }
    
    // 更新任务状态和位置
    const result = await client.query(`
      UPDATE tasks 
      SET 
        status = $1,
        board_position = $2,
        completed = CASE WHEN $1 = 'done' THEN true ELSE false END,
        updated_at = NOW()
      WHERE id = $3 AND user_id = $4
      RETURNING *
    `, [status, position, taskId, userId])
    
    if (result.rows.length === 0) {
      throw new Error('Task not found or no permission')
    }
    
    return result.rows[0]
  } catch (error) {
    console.error('更新任务状态失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 批量更新任务位置
export interface TaskPositionUpdate {
  taskId: string
  status: string
  position: number
}

export async function batchUpdateTaskPositions(
  pool: Pool,
  userId: string,
  updates: TaskPositionUpdate[]
) {
  const client = await pool.connect()
  
  try {
    await client.query('BEGIN')
    
    for (const update of updates) {
      await client.query(`
        UPDATE tasks 
        SET 
          status = $1,
          board_position = $2,
          completed = CASE WHEN $1 = 'done' THEN true ELSE false END,
          updated_at = NOW()
        WHERE id = $3 AND user_id = $4
      `, [update.status, update.position, update.taskId, userId])
    }
    
    await client.query('COMMIT')
    
    console.log(`✅ 批量更新 ${updates.length} 个任务位置`)
    return { success: true, updated: updates.length }
    
  } catch (error) {
    await client.query('ROLLBACK')
    console.error('批量更新任务位置失败:', error)
    throw error
  } finally {
    client.release()
  }
}
